/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package edu.uoc.tdp.pac3.persistence.dao.impl;

import edu.uoc.tdp.pac3.configuration.PropertiesConfiguration;
import edu.uoc.tdp.pac3.domain.Request;
import edu.uoc.tdp.pac3.domain.RequestType;
import edu.uoc.tdp.pac3.persistence.dao.IRequestDao;
import edu.uoc.tdp.pac3.persistence.exceptions.ErrorConnexioBD;
import edu.uoc.tdp.pac3.persistence.exceptions.ErrorSQL;
import edu.uoc.tdp.pac3.persistence.exceptions.ItemNoTrobat;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.omg.PortableServer.REQUEST_PROCESSING_POLICY_ID;

/**
 *
 * @author Gerard
 */
public class RequestDao extends BaseDao<Request> implements IRequestDao {

    public RequestDao() {
     super(PropertiesConfiguration.getInstance(), "tb_request");
    }

    @Override
    public List<Request> findAll() {
        List<Request> list = null;
        try {
            openConnection();
            ResultSet rs = buscar(getTableName());
            list = new ArrayList<Request>();
            while (rs.next()) {
                Request request = new Request();
                request.setAreaId(rs.getInt("area_id"));
                request.setCreationDate(rs.getDate("creation_date"));
                request.setDescription(rs.getString("description"));
                request.setId(rs.getInt("request_id"));
                request.setRequestType(RequestType.getTypeFromChar(rs.getString("is_request_solved").charAt(0)));
                request.setUserId(rs.getInt("user_id"));
                list.add(request);
            }
        } catch (SQLException ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ItemNoTrobat ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ErrorSQL ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ErrorConnexioBD ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            tancarConnexio();
        }
        return list;
    }

    @Override
    public boolean insert(Request entity) {
        boolean result = false;
        try {
            openConnection();
            begin();
            StringBuilder sb = new StringBuilder();
            sb.append("INSERT INTO ").append(getTableName()).append(" (user_id,area_id,creation_date,description,is_request_solved) VALUES(?,?,?,?,?)");
            PreparedStatement statement = connexio.prepareStatement(sb.toString());

            if(entity.getUserId() != null) {
                statement.setInt(1, entity.getUserId());
            }
            
            if(entity.getAreaId() != null) {
                statement.setInt(2, entity.getAreaId());
            }
            
            if(entity.getCreationDate() != null){
                statement.setDate(3, new Date(entity.getCreationDate().getTime()));
            }
            
            if(entity.getDescription() != null){
                statement.setString(4, entity.getDescription());
            }
            
            if(entity.getRequestType() != null){
                statement.setString(5, entity.getRequestType().getId().toString());
            }
            
            int i = statement.executeUpdate();
            if (i > 0) {
                result = true;
            }
            commit();
        } catch (ErrorSQL ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ErrorConnexioBD ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            tancarConnexio();
        }
        return result;
    }

    @Override
    public boolean update(Request entity) {
         boolean result = false;
        try {
            openConnection();
            begin();
            StringBuilder sb = new StringBuilder();
            sb.append("UPDATE ").append(getTableName()).append("SET ")  .append("user_id=?, ")
                                                                        .append("area_id=?, ")
                                                                        .append("creation_date=?, ")
                                                                        .append("description=?, ")
                                                                        .append("is_request_solved=? ")
                                                       .append("WHERE request_id=?");
            PreparedStatement statement = connexio.prepareStatement(sb.toString());
            
            
            if(entity.getUserId() != null){
                statement.setInt(1, entity.getUserId());
            }
            
            if(entity.getAreaId() != null){
                statement.setInt(2, entity.getAreaId());
            }
            
            if(entity.getCreationDate() != null){
                statement.setDate(3, new Date(entity.getCreationDate().getTime()));
            }
            
            if(entity.getDescription() != null){
                statement.setString(4, entity.getDescription());
            }
            
            if(entity.getRequestType() != null){
                statement.setString(5, entity.getRequestType().getId().toString());
            }

            statement.setInt(6, entity.getId());

            int i = statement.executeUpdate();
            if (i > 0) {
                result = true;
            }
            commit();
        } catch (ErrorSQL ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ErrorConnexioBD ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            tancarConnexio();
        }
        return result;
    }

    @Override
    public boolean delete(Request entity) {
        boolean result = false;
        try {
            openConnection();
            begin();
            StringBuilder sb = new StringBuilder();
            sb.append("DELETE FROM ").append(getTableName()).append(" WHERE request_id=?");
            PreparedStatement statement = connexio.prepareStatement(sb.toString());
            statement.setInt(1, entity.getId());
            int i = statement.executeUpdate();
            if (i > 0) {
                result = true;
            }
            commit();
        } catch (ErrorSQL ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ErrorConnexioBD ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            tancarConnexio();
        }
        return result;    }

    @Override
    public Request findById(Integer id) {
        Request request = null;
        try {
            openConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT * FROM ").append(getTableName()).append(" WHERE request_id=?");
            PreparedStatement statement = connexio.prepareStatement(sb.toString());
            statement.setInt(1, id);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                request = new Request();
                request.setAreaId(rs.getInt("area_id"));
                request.setCreationDate(rs.getDate("creation_date"));
                request.setDescription(rs.getString("description"));
                request.setId(rs.getInt("request_id"));
                request.setRequestType(RequestType.getTypeFromChar(rs.getString("is_request_solved").charAt(0)));
                request.setUserId(rs.getInt("user_id"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ErrorConnexioBD ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            tancarConnexio();
        }
        return request;
    }
    
    public List<Request> findRequestsByType(RequestType requestType){
        List<Request> requests = null;
        try {
            openConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT * FROM ").append(getTableName()).append(" WHERE is_request_solved=?");
            PreparedStatement statement = connexio.prepareStatement(sb.toString());
            statement.setString(1, requestType.getId().toString());
            ResultSet rs = statement.executeQuery();
            requests = new ArrayList<Request>();
            while (rs.next()) {
                Request request = new Request();
                request.setAreaId(rs.getInt("area_id"));
                request.setCreationDate(rs.getDate("creation_date"));
                request.setDescription(rs.getString("description"));
                request.setId(rs.getInt("request_id"));
                request.setRequestType(RequestType.getTypeFromChar(rs.getString("is_request_solved").charAt(0)));
                request.setUserId(rs.getInt("user_id"));
                requests.add(request);
            }
        } catch (SQLException ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ErrorConnexioBD ex) {
            Logger.getLogger(AreaDao.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            tancarConnexio();
        }
        return requests;
    }
}
